/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package Deprecated;

import com.lcfgly.Exception.SQLEmptyException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

/**
 * 基本查询操作封装
 * @author lcfgly
 */
@Deprecated
@Repository
public class DbSelect {
 
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    String select="*";
    String from="";
    String wheresql="";
    String joinsql="";
    String order_by="";
    String limit="";
    List<Object> where=new ArrayList<Object>();
    
    /**
     * SELECT
     * @param select
     * @return 
     */
    public DbSelect select(String select)
    {
        this.select=select;
        return this;
    }
    
    /**
     * TABLE
     * @param table
     * @return 
     */
    public DbSelect from(String table)
    {
        from=table;
        return this;
    }
    
    /**
     * JOIN
     * @param table
     * @param arg0
     * @return 
     */
    public DbSelect join(String table,String arg0)
    {
        if(!table.isEmpty() && !arg0.isEmpty())
            joinsql+=" JOIN "+table+" ON "+arg0;
        return this;
    }
    
    /**
     * JOIN
     * @param table
     * @param arg0
     * @param type
     * @return 
     */
    public DbSelect join(String table,String arg0,String type)
    {
        if(type == null|| type.isEmpty() ) 
            join(table, arg0);
        else
        {
            if(type.equalsIgnoreCase("left") || type.equalsIgnoreCase("right"))
                joinsql+=" "+type+" JOIN "+table+" ON "+arg0;
        }
        return this;
    }
   
    /**
     * WHERE
     * @param comt
     * @param res
     * @param type
     * @param and
     * @return 
     */
    public DbSelect where(String comt,Object res,String type,String and)
    {
        if( and == null || and.equalsIgnoreCase("and") || and.equals("or"))
        {
            if(comt.isEmpty() || res == null)
                return this;
            if(wheresql.isEmpty())
                wheresql=" WHERE ";
            else
                wheresql+=" "+and+" ";
            wheresql+=comt+" "+type+" ? ";
            where.add(res);
        }
        return this;
    }
    
    /**
     * WHERE
     * @param whe
     * @param type
     * @param and
     * @return 
     */
    public DbSelect where(HashMap<String,Object> whe,String type,String and)
    {
        if(and.equalsIgnoreCase("and") || and.equals("or"))
        {
            if(!whe.isEmpty())
            {
                if(wheresql.isEmpty())
                    wheresql=" WHERE ";
                Collection<String> keys=whe.keySet();
                for (String string : keys) {
                    wheresql+=" "+and+" ";
                    wheresql+=string+" "+type+" ? ";
                    where.add(whe.get(string));
                }
            }
        }
        return this;
    }
    
    /**
     * WHERE IN
     * @param comt
     * @param arg0
     * @param type
     * @param and
     * @return 
     */
    public DbSelect where_in(String comt,Object[] arg0,String type,String and)
    {
        if((type.trim().equalsIgnoreCase("in") || type.trim().equalsIgnoreCase("not in")) && (and.equalsIgnoreCase("and") || and.equals("or")))
        {
            if(!comt.isEmpty() && arg0.length>0)
            {
                if(wheresql.isEmpty())
                    wheresql=" WHERE "+comt+" "+type+" (";
                else
                    wheresql+=" "+and+" "+comt+" "+type+" (";
                Integer xInteger=arg0.length;
                for (int i = 0; i < xInteger; i++) {
                    if(i==xInteger-1)
                        wheresql+=" ? ) ";
                    else
                        wheresql+=" ? ,";
                    where.add(arg0[i]);
                }
            }
        }
        return this;
    }
    
    /**
     * ORDER BY
     * @param order
     * @return 
     */
    public DbSelect order_by(String order)
    {
        if(!order.isEmpty()) 
            order_by=order;
        return this;
    }
    
    /**
     * LIMIT
     * @param count
     * @return 
     */
    public DbSelect limit(int count)
    {
        limit=String.valueOf(count);
        return this;
    }
    
    /**
     * LIMIT
     * @param start
     * @param count
     * @return 
     */
    public DbSelect limit(int start,int count)
    {
        limit=start+","+count;
        return this;
    }
    
    /**
     * 查询封装
     * @param conn
     * @return
     * @throws SQLException 
     */
    private String query()
    {
        String sql=" SELECT "+select+" FROM "+from;
        if(!joinsql.isEmpty())
            sql+=joinsql;
        if(!wheresql.isEmpty())
            sql+=wheresql;
        if(!order_by.isEmpty())
        {
            sql+=" ORDER BY ? ";
            where.add(order_by);
        }
        if(!limit.isEmpty())
        {
            sql+=" LIMIT "+limit;
        }
        return sql;
    }
    
    /**
     * 返回多条结果
     * @return
     * @throws SQLException 
     */
    public List result() throws SQLException
    {
        Object[] params=new Object[where.size()];
        int i=0;
        for (Object object : where) {
            params[i++]=object;
        }
        try{
            return jdbcTemplate.queryForList(query(),params);
        }catch(EmptyResultDataAccessException e){
            throw new SQLEmptyException(e);
        }catch(DataAccessException e){
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 返回单条结果
     * @return
     * @throws SQLException 
     */
    public Map resultone() throws SQLException
    {
        order_by="";
        limit="1";
        Object[] params=new Object[where.size()];
        int i=0;
        for (Object object : where) {
            params[i++]=object;
        }
        try{
            return jdbcTemplate.queryForMap(query(),params);
        }catch(EmptyResultDataAccessException e){
            throw new SQLEmptyException(e);
        }catch(DataAccessException e){
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 获取数据表数据总数
     * @param table
     * @return
     * @throws SQLException 
     */
    public int getrownums(String table) throws SQLException
    {
        try{
            return jdbcTemplate.queryForObject("SELECT count(*) FROM"+table, Integer.class);
        }catch(DataAccessException e){
            throw new SQLException(e.getMessage());
        }
    }
    
    /**
     * 清空配置
     */
    public void clear()
    {
        select="*";
        from="";
        wheresql="";
        joinsql="";
        order_by="";
        limit="";
        where.clear();
    }
    
}
